﻿using Dapper;
using Microsoft.Extensions.Options;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace JX.Dapper
{
	/// <summary>
	/// 抽象类：Dapper数据库操作上下文
	/// </summary>
	public abstract class DapperDBContext : IContext
	{
		#region 属性、构造器
		private IDbConnection _connection;
		private IDbTransaction _transaction;
		private int? _commandTimeout = null;
		private readonly DapperDBContextOptions _options;

		/// <summary>
		/// 事务是否启动
		/// </summary>
		public bool IsTransactionStarted { get; private set; }

		/// <summary>
		/// 构造器：通过参数配置类初始化属性。
		/// 1、赋值给内部属性。
		/// 2、通过连接字符串创建数据库连接类，并打开数据库连接。
		/// </summary>
		/// <param name="optionsAccessor">参数配置类</param>
		protected DapperDBContext(IOptions<DapperDBContextOptions> optionsAccessor)
		{
			_options = optionsAccessor.Value;
			_commandTimeout = _options.CommandTimeout;
			_connection = CreateConnection(_options.Configuration, _options.ProviderName);
			_connection.Open();

			DebugPrint("Connection started.");
		}
		#endregion

		/// <summary>
		/// 抽象方法：创建数据库连接类
		/// </summary>
		/// <param name="connectionString">数据库连接字符串</param>
		/// <param name="providerName">数据库提供者类型：sqlserver、mysql</param>
		/// <returns></returns>
		protected abstract IDbConnection CreateConnection(string connectionString, string providerName = "sqlserver");

		#region 事务
		/// <summary>
		/// 开启事务
		/// </summary>
		public void BeginTransaction()
		{
			if (IsTransactionStarted)
				throw new InvalidOperationException("Transaction is already started.");

			_transaction = _connection.BeginTransaction();
			IsTransactionStarted = true;

			DebugPrint("Transaction started.");
		}

		/// <summary>
		/// 提交事务
		/// </summary>
		public void Commit()
		{
			if (!IsTransactionStarted)
				throw new InvalidOperationException("No transaction started.");

			_transaction.Commit();
			_transaction = null;

			IsTransactionStarted = false;

			DebugPrint("Transaction committed.");
		}

		/// <summary>
		/// 回滚事务
		/// </summary>
		public void Rollback()
		{
			if (!IsTransactionStarted)
				throw new InvalidOperationException("No transaction started.");

			_transaction.Rollback();
			_transaction.Dispose();
			_transaction = null;

			IsTransactionStarted = false;

			DebugPrint("Transaction rollbacked and disposed.");
		}

		#endregion

		#region 运行SQL语句,返回受影响的行数
		/// <summary>
		/// 执行SQL语句并返回受影响的行数
		/// </summary>
		/// <param name="sql">SQL语句，例：select id from Area where id in @ids</param>
		/// <param name="param">用于此查询的参数（例：new { Id = id }，如果是IN操作，传入：new { ids = new int[2] { 1, 2 } }）</param>
		/// <param name="commandType">是存储过程还是SQL语句</param>
		/// <returns></returns>
		public int Execute(string sql, object param = null, CommandType commandType = CommandType.Text)
		{
			return _connection.Execute(sql, param, _transaction, _commandTimeout, commandType);
		}
		/// <summary>
		/// 执行SQL语句并返回受影响的行数（异步）
		/// </summary>
		/// <param name="sql">SQL语句，例：select id from Area where id in @ids</param>
		/// <param name="param">用于此查询的参数（例：new { Id = id }，如果是IN操作，传入：new { ids = new int[2] { 1, 2 } }）</param>
		/// <param name="commandType">是存储过程还是SQL语句</param>
		/// <returns></returns>
		public async Task<int> ExecuteAsync(string sql, object param = null, CommandType commandType = CommandType.Text)
		{
			return await _connection.ExecuteAsync(sql, param, _transaction, _commandTimeout, commandType);
		}
		#endregion

		#region 执行SQL语句返回第一行第一列的值
		/// <summary>
		/// 返回带参数的SQL语句执行结果的第一行第一列的值
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="sql">SQL语句，例：select id from Area where id in @ids</param>
		/// <param name="param">用于此查询的参数（例：new { Id = id }，如果是IN操作，传入：new { ids = new int[2] { 1, 2 } }）</param>
		/// <param name="commandType">是存储过程还是SQL语句</param>
		/// <returns></returns>
		public T ExeSQLScalar<T>(string sql, object param = null, CommandType commandType = CommandType.Text)
		{
			return _connection.ExecuteScalar<T>(sql, param, _transaction, _commandTimeout, commandType);
		}
		/// <summary>
		/// 返回带参数的SQL语句执行结果的第一行第一列的值（异步）
		/// </summary>
		/// <param name="sql">SQL语句，例：select id from Area where id in @ids</param>
		/// <param name="param">用于此查询的参数（例：new { Id = id }，如果是IN操作，传入：new { ids = new int[2] { 1, 2 } }）</param>
		/// <param name="commandType">是存储过程还是SQL语句</param>
		/// <returns></returns>
		public async Task<T> ExeSQLScalarAsync<T>(string sql, object param = null, CommandType commandType = CommandType.Text)
		{
			return await _connection.ExecuteScalarAsync<T>(sql, param, _transaction, _commandTimeout, commandType);
		}
		#endregion

		#region 执行SQL语句并返回T
		/// <summary>
		/// 执行SQL语句并返回对象
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="sql">SQL语句，例：select id from Area where id in @ids</param>
		/// <param name="param">用于此查询的参数（例：new { Id = id }，如果是IN操作，传入：new { ids = new int[2] { 1, 2 } }）</param>
		/// <param name="commandType">是存储过程还是SQL语句</param>
		/// <returns></returns>
		public T QueryFirstOrDefault<T>(string sql, object param = null, CommandType commandType = CommandType.Text)
		{
			return _connection.QueryFirstOrDefault<T>(sql, param, _transaction, _commandTimeout, commandType);
		}
		/// <summary>
		/// 执行SQL语句并返回对象（异步）
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="sql">SQL语句，例：select id from Area where id in @ids</param>
		/// <param name="param">用于此查询的参数（例：new { Id = id }，如果是IN操作，传入：new { ids = new int[2] { 1, 2 } }）</param>
		/// <param name="commandType">是存储过程还是SQL语句</param>
		/// <returns></returns>
		public async Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null, CommandType commandType = CommandType.Text)
		{
			return await _connection.QueryFirstOrDefaultAsync<T>(sql, param, _transaction, _commandTimeout, commandType);
		}
		#endregion

		#region 执行SQL语句并返回T列表
		/// <summary>
		/// 执行SQL语句并返回对象列表
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="sql">SQL语句，例：select id from Area where id in @ids</param>
		/// <param name="param">用于此查询的参数（例：new { Id = id }，如果是IN操作，传入：new { ids = new int[2] { 1, 2 } }）</param>
		/// <param name="commandType">是存储过程还是SQL语句</param>
		/// <returns></returns>
		public IEnumerable<T> Query<T>(string sql, object param = null, CommandType commandType = CommandType.Text)
		{
			return _connection.Query<T>(sql, param, _transaction, true, _commandTimeout, commandType);
		}
		/// <summary>
		/// 执行SQL语句并返回对象列表（异步）
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="sql">SQL语句，例：select id from Area where id in @ids</param>
		/// <param name="param">用于此查询的参数（例：new { Id = id }，如果是IN操作，传入：new { ids = new int[2] { 1, 2 } }）</param>
		/// <param name="commandType">是存储过程还是SQL语句</param>
		/// <returns></returns>
		public async Task<IEnumerable<T>> QueryAsync<T>(string sql, object param = null, CommandType commandType = CommandType.Text)
		{
			return await _connection.QueryAsync<T>(sql, param, _transaction, _commandTimeout, commandType);
		}

		/// <summary>
		/// 使用2种输入类型执行多重映射查询。这将返回一个单一类型，通过映射从原始类型组合而成。
		/// </summary>
		/// <typeparam name="TFirst">记录集中的第一个类型</typeparam>
		/// <typeparam name="TSecond">记录集中的第二个类型</typeparam>
		/// <typeparam name="TReturn">要返回的组合类型</typeparam>
		/// <param name="sql">SQL语句</param>
		/// <param name="map">将行类型映射到返回类型的函数</param>
		/// <param name="param">用于此查询的参数（例：new { Id = id }）</param>
		/// <param name="splitOn">我们应该拆分并从中读取第二个对象的字段（默认值：“Id”）。</param>
		/// <param name="commandType">是存储过程还是SQL语句</param>
		/// <returns></returns>
		public IEnumerable<TReturn> Query<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map, object param = null, string splitOn = "Id", CommandType commandType = CommandType.Text)
		{
			return _connection.Query(sql, map, param, _transaction, true, splitOn, _commandTimeout, commandType);
		}
		/// <summary>
		/// 使用2种输入类型执行异步多重映射查询。这将返回一个单一类型，通过映射从原始类型组合而成。（异步）
		/// </summary>
		/// <typeparam name="TFirst">记录集中的第一个类型</typeparam>
		/// <typeparam name="TSecond">记录集中的第二个类型</typeparam>
		/// <typeparam name="TReturn">要返回的组合类型</typeparam>
		/// <param name="sql">SQL语句</param>
		/// <param name="map">将行类型映射到返回类型的函数</param>
		/// <param name="param">用于此查询的参数（例：new { Id = id }）</param>
		/// <param name="splitOn">我们应该拆分并从中读取第二个对象的字段（默认值：“Id”）。</param>
		/// <param name="commandType">是存储过程还是SQL语句</param>
		/// <returns></returns>
		public async Task<IEnumerable<TReturn>> QueryAsync<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map, object param = null, string splitOn = "Id", CommandType commandType = CommandType.Text)
		{
			return await _connection.QueryAsync(sql, map, param, _transaction, true, splitOn, _commandTimeout, commandType);
		}
		#endregion

		#region 运行SQL语句,得到DataReader对象
		/// <summary>
		/// 运行SQL语句,得到DataReader对象,使用完后显示调用DataReader的Close()方法.
		/// </summary>
		/// <param name="sql">SQL语句，例：select id from Area where id in @ids</param>
		/// <param name="param">用于此查询的参数（例：new { Id = id }，如果是IN操作，传入：new { ids = new int[2] { 1, 2 } }）</param>
		/// <param name="commandType">是存储过程还是SQL语句</param>
		/// <returns></returns>
		public IDataReader GetDataReader(string sql, object param = null, CommandType commandType = CommandType.Text)
		{
			return _connection.ExecuteReader(sql, param, _transaction, _commandTimeout, commandType);
		}
		/// <summary>
		/// 运行SQL语句,得到DataReader对象,使用完后显示调用DataReader的Close()方法.
		/// </summary>
		/// <param name="sql">SQL语句，例：select id from Area where id in @ids</param>
		/// <param name="param">用于此查询的参数（例：new { Id = id }，如果是IN操作，传入：new { ids = new int[2] { 1, 2 } }）</param>
		/// <param name="commandType">是存储过程还是SQL语句</param>
		/// <returns></returns>
		public async Task<IDataReader> GetDataReaderAsync(string sql, object param = null, CommandType commandType = CommandType.Text)
		{
			return await _connection.ExecuteReaderAsync(sql, param, _transaction, _commandTimeout, commandType);
		}
		#endregion

		#region 运行SQL语句,得到DataTable对象
		/// <summary>
		/// 运行SQL语句,得到DataTable对象
		/// </summary>
		/// <param name="sql">SQL语句，例：select id from Area where id in @ids</param>
		/// <param name="param">用于此查询的参数（例：new { Id = id }，如果是IN操作，传入：new { ids = new int[2] { 1, 2 } }）</param>
		/// <param name="commandType">是存储过程还是SQL语句</param>
		/// <returns></returns>
		public DataTable GetDataTable(string sql, object param = null, CommandType commandType = CommandType.Text)
		{
			var dr = _connection.ExecuteReader(sql, param, _transaction, _commandTimeout, commandType);
			DataTable dt = new DataTable();
			dt.Load(dr);
			return dt;
		}
		/// <summary>
		/// 运行SQL语句,得到DataTable对象
		/// </summary>
		/// <param name="sql">SQL语句，例：select id from Area where id in @ids</param>
		/// <param name="param">用于此查询的参数（例：new { Id = id }，如果是IN操作，传入：new { ids = new int[2] { 1, 2 } }）</param>
		/// <param name="commandType">是存储过程还是SQL语句</param>
		/// <returns></returns>
		public async Task<DataTable> GetDataTableAsync(string sql, object param = null, CommandType commandType = CommandType.Text)
		{
			var dr = await _connection.ExecuteReaderAsync(sql, param, _transaction, _commandTimeout, commandType);
			DataTable dt = new DataTable();
			dt.Load(dr);
			return dt;
		}
		#endregion

		/// <summary>
		/// 销毁对象
		/// </summary>
		public void Dispose()
		{
			if (IsTransactionStarted)
				Rollback();

			_connection.Close();
			_connection.Dispose();
			_connection = null;

			DebugPrint("Connection closed and disposed.");
		}

		private void DebugPrint(string message)
		{
#if DEBUG
			Debug.Print(">>> UnitOfWorkWithDapper - Thread {0}: {1}", Thread.CurrentThread.ManagedThreadId, message);
#endif
		}
	}
}
